
USE [HTQLGiaoDuc];
if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CHUYEN_NGANH') and o.name = 'FK_CHUYEN_N_REFERENCE_KHOA')
alter table CHUYEN_NGANH
   drop constraint FK_CHUYEN_N_REFERENCE_KHOA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CT_PHIEUDIEUCHINHDIEM') and o.name = 'FK_CT_PHIEU_REFERENCE_PHIEU_DI')
alter table CT_PHIEUDIEUCHINHDIEM
   drop constraint FK_CT_PHIEU_REFERENCE_PHIEU_DI
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CT_PHIEU_DIEUCHINHDIEM_CUOIKY') and o.name = 'FK_CT_PHIEU_REFERENCE_PHIEU_CK')
alter table CT_PHIEU_DIEUCHINHDIEM_CUOIKY
   drop constraint FK_CT_PHIEU_REFERENCE_PHIEU_CK
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('GIAO_VIEN') and o.name = 'FK_GIAO_VIE_REFERENCE_CHUYEN_N')
alter table GIAO_VIEN
   drop constraint FK_GIAO_VIE_REFERENCE_CHUYEN_N
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('HOC_PHAN') and o.name = 'FK_HOC_PHAN_REFERENCE_KHOA')
alter table HOC_PHAN
   drop constraint FK_HOC_PHAN_REFERENCE_KHOA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('LOP_HOC') and o.name = 'FK_LOP_HOC_REFERENCE_HOC_PHAN')
alter table LOP_HOC
   drop constraint FK_LOP_HOC_REFERENCE_HOC_PHAN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('LOP_HOC') and o.name = 'FK_LOP_HOC_REFERENCE_GIAO_VIE')
alter table LOP_HOC
   drop constraint FK_LOP_HOC_REFERENCE_GIAO_VIE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SINH_VIEN') and o.name = 'FK_SINH_VIE_REFERENCE_KHOA')
alter table SINH_VIEN
   drop constraint FK_SINH_VIE_REFERENCE_KHOA
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SV_HOC_LOP') and o.name = 'FK_SV_HOC_L_REFERENCE_LOP_HOC')
alter table SV_HOC_LOP
   drop constraint FK_SV_HOC_L_REFERENCE_LOP_HOC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SV_HOC_LOP') and o.name = 'FK_SV_HOC_L_REFERENCE_SINH_VIE')
alter table SV_HOC_LOP
   drop constraint FK_SV_HOC_L_REFERENCE_SINH_VIE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CHUNG_NHAN')
            and   type = 'U')
   drop table CHUNG_NHAN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CHUYEN_NGANH')
            and   type = 'U')
   drop table CHUYEN_NGANH
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CT_PHIEUDIEUCHINHDIEM')
            and   type = 'U')
   drop table CT_PHIEUDIEUCHINHDIEM
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CT_PHIEU_DIEUCHINHDIEM_CUOIKY')
            and   type = 'U')
   drop table CT_PHIEU_DIEUCHINHDIEM_CUOIKY
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DON_PHUC_KHAO')
            and   type = 'U')
   drop table DON_PHUC_KHAO
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GIAO_VIEN')
            and   type = 'U')
   drop table GIAO_VIEN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('HOC_PHAN')
            and   type = 'U')
   drop table HOC_PHAN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('HOC_PHI')
            and   type = 'U')
   drop table HOC_PHI
go

if exists (select 1
            from  sysobjects
           where  id = object_id('KHOA')
            and   type = 'U')
   drop table KHOA
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LOP_HOC')
            and   type = 'U')
   drop table LOP_HOC
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PHIEU_DIEU_CHINH_DIEM_CUOI_KY')
            and   type = 'U')
   drop table PHIEU_DIEU_CHINH_DIEM_CUOI_KY
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PHIEU_DIEU_CHINH_DIEM_GIUA_KY')
            and   type = 'U')
   drop table PHIEU_DIEU_CHINH_DIEM_GIUA_KY
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SINH_VIEN')
            and   type = 'U')
   drop table SINH_VIEN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SV_HOC_LOP')
            and   type = 'U')
   drop table SV_HOC_LOP
go

if exists (select 1
            from  sysobjects
           where  id = object_id('THONG_BAO')
            and   type = 'U')
   drop table THONG_BAO
go

/*==============================================================*/
/* Table: CHUNG_NHAN                                            */
/*==============================================================*/
create table CHUNG_NHAN (
   CHUNGNHANID          int                  identity,
   MASOSINHVIEN         varchar(7)           null,
   CHUNGNHAN            bit                  null,
   TENCHUNGNHAN         varchar(255)         null,
   constraint PK_CHUNG_NHAN primary key (CHUNGNHANID)
)
go

/*==============================================================*/
/* Table: CHUYEN_NGANH                                          */
/*==============================================================*/
create table CHUYEN_NGANH (
   CHUYENNGANHID        int                  identity,
   KHOAID               int                  null,
   TENCHUYENNGANH       varchar(255)         null,
   constraint PK_CHUYEN_NGANH primary key (CHUYENNGANHID)
)
go

/*==============================================================*/
/* Table: CT_PHIEUDIEUCHINHDIEM                                 */
/*==============================================================*/
create table CT_PHIEUDIEUCHINHDIEM (
   CTPDCDIEMID          int                  identity,
   PHIEUDIEUCHINHDIEMID int                  null,
   MASOSINHVIEN         varchar(7)           null,
   DIEMCAPNHAT          float                null,
   LOAIDIEM             varchar(50)          null,
   constraint PK_CT_PHIEUDIEUCHINHDIEM primary key (CTPDCDIEMID)
)
go

/*==============================================================*/
/* Table: CT_PHIEU_DIEUCHINHDIEM_CUOIKY                         */
/*==============================================================*/
create table CT_PHIEU_DIEUCHINHDIEM_CUOIKY (
   MACTPHIEUDCDIEMCK    int                  not null,
   PHIEUDIEUCHINHDIEMCUOIKYID int                  null,
   MASOSINHVIEN         varchar(7)           null,
   DIEM                 float                null,
   constraint PK_CT_PHIEU_DIEUCHINHDIEM_CUOI primary key (MACTPHIEUDCDIEMCK)
)
go

/*==============================================================*/
/* Table: DON_PHUC_KHAO                                         */
/*==============================================================*/
create table DON_PHUC_KHAO (
   DONPHUCKHAOID        int                  not null,
   MASOSINHVIEN         varchar(10)          null,
   KHOA                 varchar(255)         null,
   MAMONHOC             varchar(10)          null,
   TENGIAOVIEN          varchar(255)         null,
   constraint PK_DON_PHUC_KHAO primary key (DONPHUCKHAOID)
)
go

/*==============================================================*/
/* Table: GIAO_VIEN                                             */
/*==============================================================*/
create table GIAO_VIEN (
   GIAOVIENID           int                  identity,
   CHUYENNGANHID        int                  null,
   MASOGIAOVIEN         varchar(7)           null,
   TENGIAOVIEN          varchar(255)         null,
   NGAYSINH             datetime             null,
   DIACHI               varchar(255)         null,
   BOMON                varchar(255)         null,
   DIENTHOAI            varchar(10)          null,
   EMAIL                varchar(50)          null,
   constraint PK_GIAO_VIEN primary key (GIAOVIENID)
)
go

/*==============================================================*/
/* Table: HOC_PHAN                                              */
/*==============================================================*/
create table HOC_PHAN (
   HOCPHANID            int                  identity,
   KHOAID               int                  null,
   MAHOCPHAN            varchar(10)          null,
   TENHOCPHAN           varchar(255)         null,
   SOTINCHI             int                  null,
   MOTA                 varchar(255)         null,
   constraint PK_HOC_PHAN primary key (HOCPHANID)
)
go

/*==============================================================*/
/* Table: HOC_PHI                                               */
/*==============================================================*/
create table HOC_PHI (
   HOCPHIID             int                  identity,
   NAM                  int                  null,
   HOCKY                int                  null,
   MSSV                 varchar(7)           null,
   DADONGHOCPHI         bit                  null,
   constraint PK_HOC_PHI primary key (HOCPHIID)
)
go

/*==============================================================*/
/* Table: KHOA                                                  */
/*==============================================================*/
create table KHOA (
   KHOAID               int                  identity,
   MAKHOA               varchar(2)           null,
   TENKHOA              varchar(255)         null,
   constraint PK_KHOA primary key (KHOAID)
)
go

/*==============================================================*/
/* Table: LOP_HOC                                               */
/*==============================================================*/
create table LOP_HOC (
   LOPHOCID             int                  identity,
   HOCPHANID            int                  null,
   GIAOVIENID           int                  null,
   NAM                  int                  null,
   HOCKY                int                  null,
   constraint PK_LOP_HOC primary key (LOPHOCID)
)
go

/*==============================================================*/
/* Table: PHIEU_DIEU_CHINH_DIEM_CUOI_KY                         */
/*==============================================================*/
create table PHIEU_DIEU_CHINH_DIEM_CUOI_KY (
   PHIEUDIEUCHINHDIEMCUOIKYID int                  identity,
   MAKHOA               varchar(2)           null,
   TENGIAOVIEN          varchar(255)         null,
   MAMONHOC             varchar(10)          null,
   HOCKY                int                  null,
   NAMHOC               int                  null,
   SOLUONGDONDIEUCHINH  int                  null,
   constraint PK_PHIEU_DIEU_CHINH_DIEM_CUOI_ primary key (PHIEUDIEUCHINHDIEMCUOIKYID)
)
go

/*==============================================================*/
/* Table: PHIEU_DIEU_CHINH_DIEM_GIUA_KY                         */
/*==============================================================*/
create table PHIEU_DIEU_CHINH_DIEM_GIUA_KY (
   PHIEUDIEUCHINHDIEMGIUAKYID int                  identity,
   MASOGIAOVIEN         varchar(7)           null,
   TENGIAOVIEN          varchar(255)         null,
   MAHOCPHAN            varchar(10)          null,
   NIENKHOA             varchar(255)         null,
   SOLUONGDONDIEUCHINH  int                  null,
   constraint PK_PHIEU_DIEU_CHINH_DIEM_GIUA_ primary key (PHIEUDIEUCHINHDIEMGIUAKYID)
)
go

/*==============================================================*/
/* Table: SINH_VIEN                                             */
/*==============================================================*/
create table SINH_VIEN (
   SINHVIENID           int                  identity,
   KHOAID               int                  null,
   MASOSINHVIEN         varchar(7)           null,
   HOTEN                varchar(255)         null,
   NGAYSINH             datetime             null,
   CMND                 varchar(10)          null,
   DIACHI               varchar(255)         null,
   constraint PK_SINH_VIEN primary key (SINHVIENID)
)
go

/*==============================================================*/
/* Table: SV_HOC_LOP                                            */
/*==============================================================*/
create table SV_HOC_LOP (
   SV_HOC_LOP           int                  identity,
   LOPHOCID             int                  null,
   SINHVIENID           int                  null,
   DIEMGIUAKY           float                null,
   DIEMQUATRINH         float                null,
   DIEMCUOIKY           float                null,
   constraint PK_SV_HOC_LOP primary key (SV_HOC_LOP)
)
go

/*==============================================================*/
/* Table: THONG_BAO                                             */
/*==============================================================*/
create table THONG_BAO (
   THONGBAOID           int                  identity,
   NOIDUNG              text                 null,
   LINK                 varchar(255)         null,
   constraint PK_THONG_BAO primary key (THONGBAOID)
)
go

alter table CHUYEN_NGANH
   add constraint FK_CHUYEN_N_REFERENCE_KHOA foreign key (KHOAID)
      references KHOA (KHOAID)
go

alter table CT_PHIEUDIEUCHINHDIEM
   add constraint FK_CT_PHIEU_REFERENCE_PHIEU_DI foreign key (PHIEUDIEUCHINHDIEMID)
      references PHIEU_DIEU_CHINH_DIEM_GIUA_KY (PHIEUDIEUCHINHDIEMGIUAKYID)
go

alter table CT_PHIEU_DIEUCHINHDIEM_CUOIKY
   add constraint FK_CT_PHIEU_REFERENCE_PHIEU_CK foreign key (PHIEUDIEUCHINHDIEMCUOIKYID)
      references PHIEU_DIEU_CHINH_DIEM_CUOI_KY (PHIEUDIEUCHINHDIEMCUOIKYID)
go

alter table GIAO_VIEN
   add constraint FK_GIAO_VIE_REFERENCE_CHUYEN_N foreign key (CHUYENNGANHID)
      references CHUYEN_NGANH (CHUYENNGANHID)
go

alter table HOC_PHAN
   add constraint FK_HOC_PHAN_REFERENCE_KHOA foreign key (KHOAID)
      references KHOA (KHOAID)
go

alter table LOP_HOC
   add constraint FK_LOP_HOC_REFERENCE_HOC_PHAN foreign key (HOCPHANID)
      references HOC_PHAN (HOCPHANID)
go

alter table LOP_HOC
   add constraint FK_LOP_HOC_REFERENCE_GIAO_VIE foreign key (GIAOVIENID)
      references GIAO_VIEN (GIAOVIENID)
go

alter table SINH_VIEN
   add constraint FK_SINH_VIE_REFERENCE_KHOA foreign key (KHOAID)
      references KHOA (KHOAID)
go

alter table SV_HOC_LOP
   add constraint FK_SV_HOC_L_REFERENCE_LOP_HOC foreign key (LOPHOCID)
      references LOP_HOC (LOPHOCID)
go

alter table SV_HOC_LOP
   add constraint FK_SV_HOC_L_REFERENCE_SINH_VIE foreign key (SINHVIENID)
      references SINH_VIEN (SINHVIENID)
go
